package chemtools_logica.BDL.SFBean;

import chemtools_logica.Entidades.Eventoxinsumo;

import chemtools_logica.Entidades.EventoxinsumoPK;

import chemtools_logica.BDL.IL.BDL_SFEventoxinsumoLocal;
import chemtools_logica.BDL.IR.BDL_SFEventoxinsumoRemote;

import chemtools_logica.Entidades.Almacen;
import chemtools_logica.Entidades.Insumo;

import java.math.BigDecimal;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import javax.ejb.SessionContext;
import javax.ejb.Stateless;

import javax.ejb.TransactionAttribute;
import javax.ejb.TransactionAttributeType;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

@Stateless(name = "BDL_SFEventoxinsumo", mappedName = "map-BDL_SFEventoxinsumo")
public class BDL_SFEventoxinsumoBean implements BDL_SFEventoxinsumoRemote, BDL_SFEventoxinsumoLocal {
    @Resource
    SessionContext sessionContext;
    @PersistenceContext(unitName = "Chemtools_Logica")
    private EntityManager em;

    public BDL_SFEventoxinsumoBean() {
    }

    public Eventoxinsumo persistEventoxinsumo(Eventoxinsumo eventoxinsumo) {
        em.persist(eventoxinsumo);
        return eventoxinsumo;
    }

    public Eventoxinsumo mergeEventoxinsumo(Eventoxinsumo eventoxinsumo) {
        Eventoxinsumo ei = em.merge(eventoxinsumo);
        return ei;
    }
    
    public void removeEventoxinsumo(Eventoxinsumo eventoxinsumo) {
        eventoxinsumo =
            em.find(Eventoxinsumo.class,
                    new EventoxinsumoPK(eventoxinsumo.getAlmacen().getIdAlmacen(),
                                        eventoxinsumo.getEvento().getIdEvento(),
                                        eventoxinsumo.getInsumo().getIdInsumo(),
                                        eventoxinsumo.getUsuario().getIdUsuario()));
        em.remove(eventoxinsumo);
    }

    /** <code>select o from Eventoxinsumo o</code> */
    @TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
    public List<Eventoxinsumo> getEventoxinsumoFindAll() {
        return em.createNamedQuery("Eventoxinsumo.findAll", Eventoxinsumo.class).getResultList();
    }
    
    public List<Eventoxinsumo> getInsumosGenerales(){
        List<Eventoxinsumo> eventoXInsumos = new ArrayList<Eventoxinsumo>();
        
        String ejbQuery ="SELECT distinct t1.* "+
                            "FROM eventoxinsumo t1 " +
                            "INNER JOIN insumo AS ins ON t1.idInsumo = ins.idInsumo " +
                            "INNER JOIN almacen AS alm ON t1.idUsuario = alm.idAlmacen "+
                            "INNER JOIN "+
                            "( "+
                              "SELECT MAX(Fecha_Evento) maxdate, idInsumo,idUsuario "+
                              "FROM eventoxinsumo "+
                              "GROUP BY idInsumo "+
                            ") t2 "+
                              "ON t1.idInsumo = t2.idInsumo "+
                              "AND t1.Fecha_Evento = t2.maxdate" +
                              "AND t1.idEvento <> 9 "+
                              " group by idInsumo";
        System.out.println(ejbQuery);
        try{
            Query query = em.createNativeQuery(ejbQuery, Eventoxinsumo.class);
            eventoXInsumos = query.getResultList();
        }catch(Exception e)
        {
            eventoXInsumos = null;
        }
        
        return eventoXInsumos;
    }
    
    public List<Eventoxinsumo> getInsumosPorTipo(int idTipoInsumo){
        List<Eventoxinsumo> eventoXInsumos = new ArrayList<Eventoxinsumo>();
        
        String ejbQuery ="SELECT distinct t1.* "+
                            "FROM eventoxinsumo t1 " +
                            "INNER JOIN insumo AS ins ON t1.idInsumo = ins.idInsumo " +
                            "INNER JOIN almacen AS alm ON t1.idAlmacen = alm.idAlmacen "+
                            "INNER JOIN tipoinsumo AS tIns ON ins.idTipoInsumo = tIns.idTipoInsumo "+
                            "INNER JOIN "+
                            "( "+
                              "SELECT MAX(Fecha_Evento) maxdate, idInsumo,idUsuario "+
                              "FROM eventoxinsumo "+
                              "GROUP BY idInsumo "+
                            ") t2 "+
                              "ON t1.idInsumo = t2.idInsumo "+
                              "AND t1.Fecha_Evento = t2.maxdate " +
                              "AND tIns.idTipoInsumo = "+idTipoInsumo +
                              " group by idInsumo";
        System.out.println(ejbQuery);
        try{
            Query query = em.createNativeQuery(ejbQuery, Eventoxinsumo.class);
            eventoXInsumos = query.getResultList();
        }catch(Exception e)
        {
            eventoXInsumos = null;
        }
        
        return eventoXInsumos;
    }
    
    public List<Eventoxinsumo> getInsumosPorTipoTransformaciones(int idTipoInsumo){
        List<Eventoxinsumo> eventoXInsumos = new ArrayList<Eventoxinsumo>();
        
        String ejbQuery ="SELECT distinct t1.* "+
                            "FROM eventoxinsumo t1 " +
                            "INNER JOIN insumo AS ins ON t1.idInsumo = ins.idInsumo " +
                            "INNER JOIN almacen AS alm ON t1.idAlmacen = alm.idAlmacen "+
                            "INNER JOIN tipoinsumo AS tIns ON ins.idTipoInsumo = tIns.idTipoInsumo "+
                            "INNER JOIN "+
                            "( "+
                              "SELECT MAX(Fecha_Evento) maxdate, idInsumo,idUsuario "+
                              "FROM eventoxinsumo "+
                              "GROUP BY idInsumo "+
                            ") t2 "+
                              "ON t1.idInsumo = t2.idInsumo "+
                              "AND t1.Fecha_Evento = t2.maxdate " +
                              "AND tIns.idTipoInsumo = "+idTipoInsumo+
                              " AND (ins.estado = 1 OR ins.estado = 2)"+
                              " AND ins.calidad <= 2"+
                              " group by idInsumo";
        System.out.println(ejbQuery);
        try{
            Query query = em.createNativeQuery(ejbQuery, Eventoxinsumo.class);
            eventoXInsumos = query.getResultList();
        }catch(Exception e)
        {
            eventoXInsumos = null;
        }
        
        return eventoXInsumos;
    }
    
    public List<Eventoxinsumo> getInsumosporAlmacenActivo(int nidAlmacen){
        List<Eventoxinsumo> eventoXInsumos = new ArrayList<Eventoxinsumo>();
        
        String ejbQuery ="SELECT t1.* "+
                            "FROM eventoxinsumo t1 " +
                            "INNER JOIN insumo AS ins ON t1.idInsumo = ins.idInsumo " +
                            "INNER JOIN almacen AS alm ON t1.idAlmacen = alm.idAlmacen "+
                            "INNER JOIN "+
                            "( "+
                              "SELECT MAX(Fecha_Evento) maxdate, idInsumo,idUsuario "+
                              "FROM eventoxinsumo "+
                              "GROUP BY idInsumo "+
                            ") t2 "+
                              "ON t1.idInsumo = t2.idInsumo "+
                              "AND t1.Fecha_Evento = t2.maxdate " +
                              "AND (ins.Estado = 1 OR ins.Estado = 2) "+ //agregado por chespe como solucion temportal
                              //"AND t1.idEvento <> 9 "+
                            "AND t1.idAlmacen = "+nidAlmacen+
                            " group by idInsumo";
        System.out.println(ejbQuery);
        try{
            Query query = em.createNativeQuery(ejbQuery, Eventoxinsumo.class);
            eventoXInsumos = query.getResultList();
        }catch(Exception e)
        {
            eventoXInsumos = null;
        }
        
        return eventoXInsumos;
        
    }
    
    public List<Eventoxinsumo> getEventoXinsumoByInsumo(String nidInsumo){
        List<Eventoxinsumo> listaEventoXInsumo = new ArrayList<Eventoxinsumo>();
        
        String ejbQuery = "SELECT * FROM eventoxinsumo t1 " +
                          "INNER JOIN almacen AS alm ON t1.idAlmacen = alm.idAlmacen " +
                          "INNER JOIN usuario AS a ON t1.idUsuario = a.idUsuario " +
                          "INNER JOIN evento AS e ON t1.idEvento = e.idEvento " +
                          "WHERE t1.idInsumo = '"+nidInsumo+"'" +
                          "ORDER BY t1.Fecha_Evento asc";
        
        try{
            Query query = em.createNativeQuery(ejbQuery, Eventoxinsumo.class);
            listaEventoXInsumo = query.getResultList();
        }catch(Exception e)
        {
            listaEventoXInsumo = null;
        }
        
        return listaEventoXInsumo;
    }
    
    public BigDecimal getContenidoInsumoEvento(String idInsumo){
        List<Eventoxinsumo> eInsumo = new ArrayList<Eventoxinsumo>();
        String ejbQuery = "SELECT t1.* " +
                          "FROM eventoxinsumo t1 " +
                          "WHERE t1.idInsumo = '"+idInsumo+"' " +
                          "LIMIT 1";
        BigDecimal contenido = null;
        try{
            Query query = em.createNativeQuery(ejbQuery, Eventoxinsumo.class);
            eInsumo = query.getResultList();
            contenido = eInsumo.get(0).getContenido();
        }catch(Exception e){
            String h = "f";    
        }
        return contenido;
    }
}
